package Action;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;

import Utils.Common;
import Utils.Constant;
import Utils.GenUtils;
import Utils.StringUtil;


import Bean.ClipBean;
	
public class Clip{
	public boolean insertClip(ClipBean bean, Connection conn){
		String sql = "INSERT INTO clips (ClipId, ClipName, ClipDescription,ClipPath,ClipThumbnail,ClipDate,ClipCat) VALUES (?,?,?,?,?,?,?) ";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, bean.getClipId());
			stmt.setString(2, bean.getClipName());
			stmt.setString(3, bean.getClipDescription());
			stmt.setString(4, bean.getClipPath());
			stmt.setString(5, bean.getClipThumbnail());
			stmt.setString(6, StringUtil.convertDateForSQL(bean.getClipDate()));
			stmt.setString(7, bean.getClipCat());
			stmt.executeUpdate();
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	
	public boolean updateClip(ClipBean bean, Connection conn){
		String sql = "SELECT * FROM clips WHERE ClipId=?";
		
		
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, bean.getClipId());
			rs = stmt.executeQuery();
			String clipPath = "",thumb = "";
			if(rs.next()){
				clipPath = rs.getString("ClipPath");
				if(!clipPath.equals(bean.getClipPath())){
					String path = GenUtils.genClipPath(bean.getClipId(),clipPath);
					File f = new File(path);
					if(f.exists()) 
						f.delete();
				}
				thumb = rs.getString("ClipThumbnail");
				if(!thumb.equals(bean.getClipThumbnail())){
					String path = GenUtils.genPathByAccountId(bean.getClipId())+"/"+thumb;
					File f = new File(path);
					if(f.exists()) 
						f.delete();
					path = GenUtils.genPathByAccountId(bean.getClipId())+"/"+thumb.replace(".", "thumb.");
					f = new File(path);
					if(f.exists()) 
						f.delete();
				}
				
			}
			stmt.close();
			sql = "UPDATE clips SET ClipName=?, ClipDescription=?,ClipPath=?,ClipThumbnail=?,ClipDate=?,ClipCat=? WHERE ClipId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, bean.getClipName());
			stmt.setString(2, bean.getClipDescription());
			stmt.setString(3, bean.getClipPath());
			stmt.setString(4, bean.getClipThumbnail());
			stmt.setString(5, StringUtil.convertDateForSQL(bean.getClipDate()));
			stmt.setString(6, bean.getClipCat());
			stmt.setString(7, bean.getClipId());
			stmt.executeUpdate();
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	
	public String listClip(Connection conn){
		String sql = "SELECT * FROM clips ORDER BY ClipDate DESC LIMIT 20";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				ClipBean clip = new ClipBean();
				clip.setClipId(rs.getString("ClipId"));
				clip.setClipName(rs.getString("ClipName"));
				clip.setClipDescription(rs.getString("ClipDescription"));
				clip.setClipPath(rs.getString("ClipPath"));
				clip.setClipThumbnail(rs.getString("ClipThumbnail"));
				clip.setClipDate(rs.getString("ClipThumbnail"));
				clip.setClipCat(rs.getString("ClipCat"));
				xml += clip.makeXML();
			}
			return Common.makeSuccessXML(xml);
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	
	public String clipDetail(HttpServletRequest req, Connection conn){
		String sql = "SELECT * FROM clips WHERE ClipId=?";
		String clipId = req.getParameter("clipId");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, clipId);
			rs = stmt.executeQuery();
			if(rs.next()){
				ClipBean clip = new ClipBean();
				clip.setClipId(rs.getString("ClipId"));
				clip.setClipName(rs.getString("ClipName"));
				clip.setClipDescription(rs.getString("ClipDescription"));
				clip.setClipPath(rs.getString("ClipPath"));
				clip.setClipThumbnail(rs.getString("ClipThumbnail"));
				clip.setClipDate(rs.getString("ClipDate"));
				clip.setClipCat(rs.getString("ClipCat"));
				return Common.makeSuccessXML(clip.makeXML());
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
		return Common.makeSuccessXML();
	}
	public String deleteClip(HttpServletRequest req, Connection conn){
		String sql = "SELECT * FROM clips WHERE ClipId=?";
		
		String clipId = req.getParameter("clipId");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, clipId);
			
			rs = stmt.executeQuery();
			
			if(rs.next()){
				String thumbnail = rs.getString("ClipThumbnail");
				String folder = GenUtils.genPathByAccountId(clipId);
				String file = folder+Constant.FILE_SEPARATOR+thumbnail;
				File f = new File(file);
				if(f.exists()) f.delete();
				
				String clipPath = rs.getString("ClipPath");
				folder = GenUtils.genPathByClipId(clipId);
				file = folder+Constant.FILE_SEPARATOR+clipPath;
				f = new File(file);
				if(f.exists()) f.delete();
				rs.close();
				stmt.close();
				sql = "DELETE FROM clips WHERE ClipId=?";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, clipId);
				stmt.executeUpdate();
				return Common.makeSuccessXML();
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
		return Common.makeErrorXML("Error");
	}
}